package DBUntil;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;




public class DbHelper {
	static Connection conn = null;
	static PreparedStatement pstmt = null;
	static ResultSet rs = null;

	static {
		try {
			Class.forName(MyProperties.getInstance().getProperty("driver_name"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection() {

		try {
			conn = DriverManager.getConnection(MyProperties.getInstance().getProperty("url"),
					MyProperties.getInstance());
		} catch (SQLException e) {
			System.out.println("数据库连接对象获取失败:" + e.getMessage());
			e.printStackTrace();
		}
		return conn;
	}
	

	/**
	 * 释放资源
	 * 
	 * @param rs
	 * @param stmt
	 * @param connection
	 */
	public static void closeAll(ResultSet rs, Statement stmt, Connection connection) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				stmt = null;
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				connection = null;
			}
		}
	}



	/**
	 * 用户登录，查询单条语句
	 * 
	 * @param sql    sql语句字符串
	 * @param params 参数集
	 * @return
	 * @throws IOException
	 */
	public Map<String, Object> findsingle(String sql, Object... params) throws IOException {
		Map<String, Object> map = null;

		try {

			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			setParams(pstmt, params);

			rs = pstmt.executeQuery();

			// 获取所有列名
			List<String> columnNames = getColumnNames(rs);

			if (rs.next()) {
				map = new HashMap<String, Object>();
				// 循环所有列名
				for (String name : columnNames) {

					// 获取值
					Object obj = rs.getObject(name);
					if (obj == null) {
						continue;
					}
					// 返回获得的值的类型，和全路径
					//System.out.println(obj.getClass().getName());
					String type = obj.getClass().getName();
					if ("oracle.sql.BLOB".equals(type)) {

						// 图片处理
						Blob blob = (Blob) obj;
						InputStream in = blob.getBinaryStream(); // 读取数据库中图片
						byte[] bt = new byte[(int) blob.length()];
						in.read(bt);
						map.put(name, bt); // 将字节数组存入map中

					} else {
						map.put(name, rs.getObject(name));
					}
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}

		return map;
	}

	/**
	 * 
	 * @param pstmt
	 * @param params
	 * @throws SQLException
	 */
	public void setParams(PreparedStatement pstmt, Object... params) {
		if (params != null && params.length > 0) {
			for (int i = 0; i < params.length; i++) {
				try {
					pstmt.setObject(i + 1, params[i]);// 给定对象设置指定参数的值。
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private List<String> getColumnNames(ResultSet rs) throws SQLException {
		List<String> columnNames = new ArrayList<String>();
		// 获得sql语句，结果集元数据
		ResultSetMetaData data = rs.getMetaData();
		// 获得表列数
		int count = data.getColumnCount();

		// 循环将 获得列名称添加到list集合中
		for (int i = 1; i <= count; i++) {
			columnNames.add(data.getColumnName(i));
		}
		return columnNames;
	}

	/**
	 * 查询多条语句 将查询到的单条语句放入，list集合中
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws IOException
	 */
	public List<Map<String, Object>> findMutip(String sql, Object... params) throws IOException {

		Map<String, Object> map = null;

		List<Map<String, Object>> list = new ArrayList<>();
		conn = getConnection();
		try {

			pstmt = conn.prepareStatement(sql);
			setParams(pstmt, params);
			rs = pstmt.executeQuery();
			List<String> columnNames = getColumnNames(rs);

			while (rs.next()) { // 循环将查询到的多条数据存入list集合中去
				map = new HashMap<String, Object>();

				for (String name : columnNames) {

					Object obj = rs.getObject(name); // 获得对应列名的数据
					if (obj == null) {
						continue;
					}
			//		System.out.println(obj.getClass().getName());

					String type = obj.getClass().getName();// 获得数据类型路径，并判断是不是图片数据
					if ("oracle.sql.BLOB".equals(type)) {

						// 图片处理
						Blob blob = (Blob) obj;
						InputStream in = blob.getBinaryStream();
						byte[] bt = new byte[(int) blob.length()];
						in.read(bt);
						map.put(name, bt);

					} else {
						map.put(name, rs.getObject(name)); // 将列名与，这一列的数据存入到map中
					}
				}
				list.add(map);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * 单挑语句更新操作
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public int update(String sql, Object... params) throws Exception{
		int result = 0;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			setParams(pstmt, params);
			result = pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("单条语句操作失败" + e.getMessage());
		} finally {
			closeAll(null, pstmt, conn);
		}

		return result;
	}

	
	
	/**
	 * 多条语句的更新操作
	 * 
	 * @param sqls   sql语言集合
	 * @param params 变量集合
	 * @return
	 * @throws Exception
	 */
	public int updates(List<String> sqls, List<List<Object>> params) throws Exception {
		int result = 0;
		try {
			conn = getConnection();

			// 关闭自动事务提交
			conn.setAutoCommit(false);

			if (null != sqls && sqls.size() > 0) {
				for (int i = 0; i < sqls.size(); i++) {
					String sql = sqls.get(i); // 获取单条sql语句
					List<Object> param = params.get(i); // 获取变量
					pstmt = conn.prepareStatement(sql);
					if (null != param && param.size() > 0) {
						for (int j = 0; j < param.size(); j++) { // 循环进行，设置值
							pstmt.setObject(j + 1, param.get(j));
						}
					}
					// PreparedStatement对象，它必须是一个SQL数据操纵语言（DML）语句，如INSERT ，
					// UPDATE或DELETE ; 或不返回任何内容的SQL语句，例如DDL语句。
					result = pstmt.executeUpdate(); // 执行更新操作

					if (result <= 0) {
						conn.rollback(); // 事务回滚
						return 0;
					}

				}
				conn.commit(); // 手动提交事务
			}
		} catch (SQLException e) {
			conn.rollback(); // 事务回滚
			result = 0;
			e.printStackTrace();
			throw new Exception("单条语句操作失败" + e.getMessage());
		} finally {
			conn.setAutoCommit(true); // 重新开启事务的自动提交
			closeAll(null, pstmt, conn);
		}

		return result;
	}

	/**
	 * 聚合函数操作
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public double getPolymer(String sql, Object... params) {
		double result = 0;

		try {
			conn = getConnection();

			pstmt = conn.prepareStatement(sql);

			setParams(pstmt, params);

			rs = pstmt.executeQuery();

			if (rs.next()) {
				result = rs.getDouble(1); // 获取一列的值
			}
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			closeAll(rs, pstmt, conn);
		}

		return result;
	}

}
